/*
* Copyrigth 2008 Company All rigths reserved
* File Name: TS_User_CRUD.sql
* Created By:    Wenbin.Xu
* Created Date:   2013/7/8 16:32:55
* Description:    TS_User的增删改查存储过程
* Change History: 
* 
* */

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create  PROCEDURE UP_TS_User_Add
	@UserName  varchar(300),
	@Password  varchar(20),
	@Name  varchar(300),
	@Gender  bit,
	@BirthDay  datetime,
	@IdentityCard  varchar(20),
	@Mobile  varchar(18),
	@Email  varchar(50),
	@PhotoImage  varchar(500),
	@UserType  int
AS
SET NOCOUNT ON
BEGIN
	INSERT INTO TS_User
    (
        UserName,
        Password,
        Name,
        Gender,
        BirthDay,
        IdentityCard,
        Mobile,
        Email,
        PhotoImage,
        UserType
    )
	VALUES
    (
        @UserName,
        @Password,
        @Name,
        @Gender,
        @BirthDay,
        @IdentityCard,
        @Mobile,
        @Email,
        @PhotoImage,
        @UserType)
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_User_Delete
    @Idx  bigint
AS
SET NOCOUNT ON
BEGIN
	DELETE FROM  TS_User
	WHERE
    Idx=@Idx
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_User_Update
	@Idx  bigint,
	@UserName  varchar(300),
	@Password  varchar(20),
	@Name  varchar(300),
	@Gender  bit,
	@BirthDay  datetime,
	@IdentityCard  varchar(20),
	@Mobile  varchar(18),
	@Email  varchar(50),
	@PhotoImage  varchar(500),
	@UserType  int
AS
SET NOCOUNT ON
BEGIN
	UPDATE TS_User
    SET 
	UserName=@UserName,
	Password=@Password,
	Name=@Name,
	Gender=@Gender,
	BirthDay=@BirthDay,
	IdentityCard=@IdentityCard,
	Mobile=@Mobile,
	Email=@Email,
	PhotoImage=@PhotoImage,
	UserType=@UserType
    WHERE
    Idx=@Idx
END






set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_User_View
    @Idx  bigint
AS
SET NOCOUNT ON
BEGIN
	SELECT 
    Idx,
    UserName,
    Password,
    Name,
    Gender,
    BirthDay,
    IdentityCard,
    Mobile,
    Email,
    PhotoImage,
    UserType
    FROM TS_User with(nolock)
    WHERE 
    Idx=@Idx
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_User_ListByPaging
(
    @pageIndex  INT,
    @pageSize   INT ,
    @recordCount INT=0 OUTPUT 
)
AS
BEGIN
	
	--calculate start index & end index
	DECLARE @startIndex  AS INT,
	        @endIndex    AS INT
	
	SELECT @startIndex = @pageSize * (@pageIndex -1) + 1,
	       @endIndex = @pageSize * @pageIndex
	
	DECLARE @sql    NVARCHAR(max)
	DECLARE @where  NVARCHAR(2000)
	SET @sql=''	
	SET @where = ''
    
	
	--calculate total record count
	SET @sql='SELECT @recordCount=COUNT(1) FROM TS_User with(nolock) WHERE (1=1) ' + @where
	EXEC sp_executesql @sql, N'@recordCount int=0 OUTPUT' , @recordCount OUTPUT 
	
	
	SET @sql = 
	    'SELECT ROW_NUMBER () OVER (ORDER BY 
            Idx ASC
        ) RowId,
        Idx,
        UserName,
        Password,
        Name,
        Gender,
        BirthDay,
        IdentityCard,
        Mobile,
        Email,
        PhotoImage,
        UserType
        FROM TS_User with(nolock)
        WHERE (1=1) ' + @where
				
	SET @sql = 'SELECT T.* FROM (' 
	+ @sql
	+ ') T where T.RowId between @startIndex and @endIndex '

	exec sp_executesql @sql,N'@startIndex int, @endIndex int',@startIndex,@endIndex 
END